package com.xtm.exercise.utils.export;

import com.one.common.utils.DateUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Excel导出用工具类
 * 所有包括paramsMap整理、数据库读取、Excel导出的所有操作
 * 全部封装在这个类中，之后只要调用这个工具类就可以了
 */
public final class ExcelExportsUtil {

    /**
     * Controller调用方法
     * @param request 内置对象
     * @param response 内置对象
     * @param paramsMap 查询参数
     * @param service 实现IExcelExportsService接口中的方法来查询需要导出的数据
     * @throws Exception
     */
	public static void exports(HttpServletRequest request, HttpServletResponse response, Map<String, Object> paramsMap, IExcelExportsService service) throws Exception {
	    //导出相关的参数（文件名、表名、列名等）
		Map<String, String> bakMap = ExcelUtil.dealWithParams(request);
		//构建变量
		paramsMap.put("tableName", (String) bakMap.get("tableName"));
		String fileName = (String) bakMap.get("fileName") + ".xls";
		String filter = (String) bakMap.get("filter");
		String[] columns = ((String) bakMap.get("columnNames")).split(",");
		String[] titles = ((String) bakMap.get("columnCaptions")).split(",");
		if (titles.length != columns.length) {
			throw new Exception("字段名有错误");
		}
		//查询数据
		List<Map<String, Object>> data = getExportData(paramsMap, filter, columns, service);
		//生成文件并导出
		ExcelUtil.exportExcel(request, response, fileName, titles, columns, data);
	}
	/**
	 * 多表头自定义表头时，导出调用此方法
	 */

	public static void exportsSelfDesignTitle(HttpServletRequest request, HttpServletResponse response, Map<String, Object> paramsMap, IExcelExportsMutiService service) throws Exception {
	    //导出相关的参数（文件名、表名、列名等）
//		Map<String, String> bakMap = ExcelUtil.dealWithParams(request);
//		//构建变量
//		paramsMap.put("tableName", (String) bakMap.get("tableName"));
//		String fileName = (String) bakMap.get("fileName") + ".xls";
//		String filter = (String) bakMap.get("filter");
//		String[] columns = ((String) bakMap.get("columnNames")).split(",");
//		String[] titles = ((String) bakMap.get("columnCaptions")).split(",");

		paramsMap.put("tableName","edu_teacher");
		paramsMap.put("fileName","exportsSelfDesignTitle"+ DateUtils.getCurrentDate("yyyy-MM-dd HH:mm:ss"));
		paramsMap.put("filter","");
		paramsMap.put("columnNames","学号,课程0001,课程0002,课程0003,intro,name,id,avatar");
		paramsMap.put("columnCaptions","学号,课程0001,课程0002,课程0003,intro,name,id,avatar");
//		paramsMap.put("columnNames","gmt,career,is,level,intro,name,id,avatar");
//		paramsMap.put("columnCaptions","gmt,career,is,level,intro,name,id,avatar");
		String fileName = (String) paramsMap.get("fileName") + ".xls";
		String filter = (String) paramsMap.get("filter");
		String[] columns = ((String) paramsMap.get("columnNames")).split(",");
		String[] titles = ((String) paramsMap.get("columnCaptions")).split(",");

		if (titles.length != columns.length) {
			throw new Exception("字段名有错误");
		}
		//查询数据
		List<Map<String, Object>> data = getExportData(paramsMap, filter, columns, service);
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		//获取文件头
		int rowNum = getExcelHead(workbook,sheet,service,paramsMap);
		//生成文件并导出
		ExcelUtil.exprtRunningReport(request, response, fileName, titles, columns, data,null,workbook,sheet,rowNum);
	}
	/**
	 * 生成文件头信息
	 * 返回从第几行开始插入数据
	 */
	private static int getExcelHead(HSSFWorkbook workbook, HSSFSheet sheet, IExcelExportsMutiService service, Map<String, Object> paramsMap){
	    HSSFPalette palette = workbook.getCustomPalette();
	    //获取具体的文件头
		List<String[]> list = service.getHeader(sheet,paramsMap, workbook);
		if(list == null ) {
			return 0;
		}
		int rowNum = list.size();
		for(int i = 0; i < rowNum;i++){
			String [] titleAttr = list.get(i);
			 /*添加标题行*/
	        HSSFRow row1 = sheet.createRow(i);
	        // 设置单元格表单标题样式
	        HSSFCellStyle style_bt_no = workbook.createCellStyle();
	        HSSFFont font_ = workbook.createFont();
	        if(i==0){//第一行标题行样式 居中
	        	style_bt_no = ExcelUtil.getManyColumnHeaderStyle(font_ ,style_bt_no,palette);
	        }else if(i==1){//第二行，统计时间样式 居右
	        	style_bt_no = ExcelUtil.getColumnStatTimeStyle(font_ ,style_bt_no,palette);
	        }else{//标题行的其他样式 ，背景绿色
	        	style_bt_no = ExcelUtil.getMutiColumnTitlesStyle(font_ ,style_bt_no,palette);
	        }
	        //插入单元格的值
	        for (int j = 0; j < titleAttr.length; j++) {
	            HSSFCell cell = row1.createCell(j);
	            cell.setCellValue(titleAttr[j]);
	            cell.setCellStyle(style_bt_no);
	            sheet.setColumnWidth(j, titleAttr[j].getBytes().length * 256);
	         }
		}
		return rowNum;
	}

	/**
	 * 查询数据
	 * @param paramsMap
	 * @param filter
	 * @param columns
	 * @param service
	 * @return
	 */
	private static List<Map<String, Object>> getExportData(Map<String, Object> paramsMap, String filter, String[] columns, IExcelExportsService service) {
	    //增加条数限制
		paramsMap.put("currPage", 0);
		paramsMap.put("pageSize", 60000);
		List<String> columnList = new ArrayList<>();
		for (int i = 0, len = columns.length; i < len; i++) {
			columnList.add(columns[i]);
			columns[i] = toLowerCamel(columns[i]);
		}
		paramsMap.put("columnList", columnList);
		//条件框的参数
		if (StringUtils.isNotEmpty(filter)) {
			String[] fs = filter.split("&");
			String filterName;
			String filterValue;
			for (int i = 0; i < fs.length; i++) {
				String[] kv = fs[i].split("=");
				if(kv.length>=2){
					filterName = kv[0];
					filterValue = kv[1];
					if((service instanceof IExcelExportsMutiService) && paramsMap.containsKey(filterName)){
						//已经存在key值时，不覆盖
					}else{
						paramsMap.put(filterName, filterValue);//只有不存在该key时，才加入，防止覆盖问题
					}
				}
			}
			if(paramsMap.containsKey("sorter") && StringUtils.isNotBlank(paramsMap.get("sorter").toString()) ){
				String sorter = paramsMap.get("sorter").toString();
				String[] sortArr = sorter.split("  ");
				paramsMap.put("sorter", "\""+sortArr[0]+"\""+"  "+sortArr[1]);
			}
			//initParamsMap(paramsMap);
		}
		List<Map<String, Object>> data = service.getDataForExportExcel(paramsMap);
		return data;
	}

	public static final String toLowerCamel(String input) {

		String[] tmpArr = input.split("_");
		StringBuilder sb = new StringBuilder(tmpArr[0].toLowerCase());

		for (int colIndex = 1, jLen = tmpArr.length; colIndex < jLen; colIndex++) {
			sb.append(tmpArr[colIndex].substring(0, 1).toUpperCase())
					.append(tmpArr[colIndex].substring(1).toLowerCase());
		}

		String tmpInput = sb.toString();

		return tmpInput;
	}


	//=================================================================================================================================================
	/**
     * 请不要随便修改逻辑.如有问题,请群里沟通,避免涉及到调用的地方功能异常   2020-04-07   高东胜
     * 按规定要求导出逻辑
     * 1.0前台页面书写要求:
     * var paramColumns = [{value:'APPLYER_NAME',text:'申请人'},...];
	 *					var datagrid = cube.getPageViewModelByNode($("#datagrid"));
	 *					var filter = cube.getPageViewModelByNode($("#dataSear")).getSearchParam();
	 *	            	cube.exportExcel(self.url + "export",paramColumns, datagrid, "data_table_name", filter, "用车申请信息");
	 *					(*****paramColumns里面的value 必须是数据库table 的列名			cube.exportExcel()括号里分别是导出接口、可导出内容、关联表格、表名称、过滤条件、导出文件名称*****)
     * 2.0后台SQL查询书写要求:
     * SELECT ID,
	 *       <foreach collection="columnList" index="index" item="column" open="" separator="," close="">
     *       ${column}
     *   	</foreach>
	 *	from (
	 *		SELECT APPLYER_NAME ... (*****这里要求是该表所有的字段,全部是table 的列名*****) FROM TABLE_NAME   (@标识为导出查询的主逻辑EXPORT_MAIN_SEARCH_SQL)
	 *	)AA
	 *
	 * 3.0后台逻书写要求:
     *  建议导出对应的controller-->对应的service的api接口类去实现IExcelExportsService接口,service的实现类重写getDataForExportExcel()方法.
     *  在service 里面去根据实际情况初始化查询paramsMap,通常情况初始化paramsMap 的逻辑同查询页面的初始化参数逻辑是一致的,可以进行方法抽取
     *  如果涉及到码表值的转换,请参考用车申请导出时候码表值的处理,操作方法如下:
     *  (1)controller 进入export url 的时候将要转换的码值放到paramsMap,paramsMap.put("dictList", initDictList());	//涉及到码表转换字段  (***   initDictList()  这里要求List 且里面的值为驼峰命名 比如数据来源 recFrom ***)
     *  (2)EXPORT_MAIN_SEARCH_SQL 该段主查询SQL查询的列多查  码值即可   比如要查询数据来源   多查一列名为 RECFROM 即可   (*** 这里要求去码值下划线,且全部大写 ***)
     *
     *
     * @param request 内置对象
     * @param response 内置对象
     * @param paramsMap 查询参数
     * @param service 实现IExcelExportsService接口中的方法来查询需要导出的数据
     * @throws Exception
     */
	public static void export(HttpServletRequest request, HttpServletResponse response, Map<String, Object> paramsMap, IExcelExportsService service) throws Exception {
	    //导出相关的参数（文件名、表名、列名等）
		Map<String, String> bakMap = ExcelUtil.dealWithParams(request);
		//构建变量
		paramsMap.put("tableName", (String) bakMap.get("tableName"));
		String fileName = (String) bakMap.get("fileName") + ".xls";
		String filter = (String) bakMap.get("filter");
		String[] columns = ((String) bakMap.get("columnNames")).split(",");
		String[] titles = ((String) bakMap.get("columnCaptions")).split(",");
		if (titles.length != columns.length) {
			throw new Exception("字段名有错误");
		}
		//查询数据
		List<Map<String, Object>> data = getExportDataFromService(paramsMap, filter, columns, service);
		//生成文件并导出
		ExcelUtil.exportExcel(request, response, fileName, titles, columns, data);
	}

	/**
	 * 查询数据
	 * @param paramsMap
	 * @param filter
	 * @param columns
	 * @param service
	 * @return
	 */
	private static List<Map<String, Object>> getExportDataFromService(Map<String, Object> paramsMap, String filter, String[] columns, IExcelExportsService service) {
	    //增加条数限制
		paramsMap.put("currPage", 0);
		paramsMap.put("pageSize", 60000);
		List<String> dictList = (List) paramsMap.get("dictList"); // 查询列名以及导出excle
		if (dictList == null || dictList.size() == 0) // 不涉及码表转换的
			dictList = new ArrayList<String>();
		List<String> columnList = new ArrayList<>();
		for (int i = 0, len = columns.length; i < len; i++) {
			String tempColumn = columns[i].replace("_", "");
			columnList.add(columns[i]);
			for (String dict : dictList) {
				if (dict.contains(":")) {
					String[] dictArr = dict.split(":");
					dict = dictArr[0];
				}

				if (tempColumn.equals(dict.toUpperCase())) {
					columns[i] = tempColumn; // 修改列-excle 的时候做比对用
					columnList.remove(columns[i]);
					columnList.add(tempColumn); // 查sql 列名-转编码的列名
				}
			}
		}
		paramsMap.put("columnList", columnList);
		//条件框的参数
		if (StringUtils.isNotEmpty(filter)) {
			String[] fs = filter.split("&");
			String filterName;
			String filterValue;
			for (int i = 0; i < fs.length; i++) {
				String[] kv = fs[i].split("=");
				filterName = kv[0];
				filterValue = kv[1];
				paramsMap.put(filterName, filterValue);
			}
			//initParamsMap(paramsMap);	这里的逻辑可以在service.getDataForExportExcel(paramsMap); 去完成
		}
		List<Map<String, Object>> data = service.getDataForExportExcel(paramsMap);
		return data;
	}


}
